﻿<?xml version="1.0" encoding="utf-8" ?>

<Queries>
  <Query Name="GetTriggerDetails" Unique1="S1.[Name]" Unique2="" ConditionType="AND">
	 <![CDATA[
	 SELECT
	S2.[name] TableName
  , S1.[name] TriggerName
  , CASE
		WHEN S2.deltrig = s1.id
			THEN
				'Delete'
		WHEN S2.instrig = s1.id
			THEN
				'Insert'
		WHEN S2.updtrig = s1.id
			THEN
				'Update'
	END 'TriggerType'
  , 'S1'
  , s1.*
  , 'S2'
  , s2.*
	FROM
		sysobjects S1
		JOIN sysobjects S2
			ON S1.parent_obj = S2.[id]
	WHERE
		S1.xtype = 'TR'
	 ]]>
  </Query>
  <Query Name="GetTableColumns" Unique1="object_id" Unique2="" ConditionType="WHERE">
	 <![CDATA[
	 SELECT * FROM sys.columns
	 ]]>
  </Query>
  <Query Name="GetColumnDetails" Unique1="table_name" Unique2="column_name" ConditionType="WHERE">
	 <![CDATA[
	 SELECT column_name 'Column Name',
	 data_type 'Data Type',
	 CHARacter_maximum_length 'Maximum Length'
	 FROM information_schema.columns
	 ]]>
  </Query>
  <Query Name="GetSPDetails" Unique1="procedure_name" Unique2="" ConditionType="STOREDPROC">
	 <![CDATA[
	 sp_HelpText
	 ]]>
  </Query>
  <Query Name="GetTableDetails" Unique1="t.name" Unique2="" ConditionType="AND">
	 <![CDATA[
	 SELECT  	u.name + '.' + t.name AS [table],
				td.value AS [table_desc],
			c.name AS [column],
			cd.value AS [column_desc]
	 FROM    	sysobjects t
	 INNER JOIN  sysusers u
	 ON		u.uid = t.uid
	 LEFT OUTER JOIN sys.extended_properties td
	 ON		td.major_id = t.id
	 AND 	td.minor_id = 0
	 AND		td.name = 'MS_Description'
	 INNER JOIN  syscolumns c
	 ON		c.id = t.id
	 LEFT OUTER JOIN sys.extended_properties cd
	 ON		cd.major_id = c.id
	 AND		cd.minor_id = c.colid
	 AND		cd.name = 'MS_Description'
	 WHERE t.type = 'u'
	 ]]>
  </Query>
  <Query Name="GetUDDTDetails" Unique1="c.id" Unique2="" ConditionType="AND">
	 <![CDATA[
	 SELECT
	 OBJECT_NAME(c.id) as TableName,
	 c.name as ColumnName,
	 c.colid As ColumnOrder,
	 st.name as UserType,
	 bt.name as BaseType
	 FROM
	 dbo.syscolumns c
	 INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
	 INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
	 WHERE
	 OBJECTPROPERTY(c.id,'ISTABLE') = 1
	 AND c.name = 'ColumnName'
	 ]]>
  </Query>
  <Query Name="GetViewDetails" Unique1="table_name" Unique2="" ConditionType="WHERE">
	 <![CDATA[
	 SELECT * FROM INFORMATION_SCHEMA.COLUMNS
	 ]]>
  </Query>
  <Query Name="GetIndexDetails" Unique1="ind.name" Unique2="" ConditionType="AND">
	 <![CDATA[
	 SELECT
	  ind.name
	 ,ind.index_id
	 ,ic.index_column_id
	 ,col.name
	 ,ind.*
	 ,ic.*
	 ,col.*
	 FROM sys.indexes ind

	 INNER JOIN sys.index_columns ic
	 ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id

	 INNER JOIN sys.columns col
	 ON ic.object_id = col.object_id and ic.column_id = col.column_id

	 INNER JOIN sys.tables t
	 ON ind.object_id = t.object_id

	 WHERE (1=1)
	 AND ind.is_primary_key = 0
	 AND ind.is_unique = 0
	 AND ind.is_unique_constraint = 0
	 AND t.is_ms_shipped = 0
	 ]]>
  </Query>
</Queries>